<?php
/**
 * 商户订单导出
 * User: 13651
 * Time: 2025/9/11 09:30
 */

namespace App\Jobs;

use App\Extensions\oss\Oss;
use App\Models\Bi\BillLog;
use App\Models\StoreAllPayWayList;
use App\Repository\OrderExportRepo;
use App\Util\ExeclUtil;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use ZipArchive;

class MerchantOrderQueryExportJob  implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    /**
     * 查询表别名
     */
    const TmpTableName = "tmp_orders";

    /**
     * 查询条件
     * @var array
     */
    protected $queryData;

    /**
     * 设置超时时间
     * @var int
     */
    public $timeout = 0;

    /**
     * 申请导出记录ID
     * @var int
     */
    protected $id;

    /**
     * 查询条件
     * @param array $queryParams
     */
    public function __construct(array $queryParams,int $id)
    {
        $this->queryData = $queryParams;
        $this->id = $id;
    }

    public function handle()
    {
        /**
         * 客户端提交的查询条件
         */
        $where = $this->queryData;

        /**
         * 下载链接
         */
        $url = '';
        Log::info('导出执行开始时间'.date("Y-m-d H:i:s", time()));
        ini_set('memory_limit', '800M');
        set_time_limit(0);
        try {
            $company = StoreAllPayWayList::query()->get()->toArray();
            $company = array_combine(array_column($company, "company"), $company);

            $title = [
                '门店ID', '门店名称', "设备ID", '收银员', '订单号', "原订单号", '订单金额', '退款金额', "实收金额", "实收净额", '费率', '手续费', '支付方式', '支付状态', '付款时间', '通道',
                '退款时间', "退款类型", '备注', "买家ID", "优惠券类型", "优惠券金额"
            ];
            $fileName = "Merchant订单导出" . ($where["month"] ?? date("Y-m-d")) . "_" . date('YmdHis') . ".csv";
            $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
            Log::info('文件路径：'.$filePath.$fileName);
            /**
             * 更新数据表状态为已完成
             */
            $log['status'] = 1;
            $log['file_name'] = explode('.',$fileName)[0].'.zip';
            $log['link'] = $url;

            /**
             *防止查询条件重复
             */
            if (!empty($where["store_id"])) {
                $storeData[] = $where["store_id"];
            }else{
                $storeData = $where['merchant_all_store_ids'];
            }

            unset($where["store_id"]);
            unset($where["merchant_all_store_ids"]);
            Log::info('服务商导出订单门店ID：',$storeData);

            /**
             * link开关
             */
            $flag = false;

            /**
             * 从新定义查询条件
             */
            foreach ($storeData as $keys => &$value) {
                $flagTitle = false;
                if ($keys == 0) {
                    if (!is_dir($filePath)) {
                        mkdir($filePath, 0777, true);
                    }
                    $file = $filePath . $fileName;
                    $fileObj = fopen($file, 'a+');
                    fwrite($fileObj, "\xEF\xBB\xBF"); // UTF-8 BOM
                    fputcsv($fileObj, $title);
                }
                Log::info('查询门店ID:'.$value);
                $where['store_id'] = $value;
                if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
                    $key = "";
                    $prefix = "";
                    $query = DB::connection();
                    $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                    $query = $query->table("orders as " . self::TmpTableName);
                } else {
                    if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                        $link = $this->emptyExcel($filePath,$fileName,$title);
                        $log['link'] = $link;
                        OrderExportRepo::getInstance()->saveData($log,$this->id);
                        return;
                    }
                    $prefix = "bi_";
                    $query = DB::connection("bi");
                    $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                    $query = $query->table(DB::raw($this->getTableName($where["start_date"], true, $prefix) ));
                }
                $this->setWhere($query, $where, self::TmpTableName);

                $query->select([
                    self::TmpTableName . ".trade_no",
                    self::TmpTableName.'.store_id',
                    self::TmpTableName.'.out_trade_no',
                    self::TmpTableName.'.created_at',
                    self::TmpTableName.'.store_name',
                    self::TmpTableName.'.device_id',
                    self::TmpTableName.'.merchant_name',
                    self::TmpTableName.'.total_amount',
                    self::TmpTableName.'.refund_amount',
                    self::TmpTableName.'.rate',
                    self::TmpTableName.'.fee_amount',
                    self::TmpTableName.'.ways_type_desc',
                    self::TmpTableName.'.pay_status_desc',
                    self::TmpTableName.'.pay_time',
                    self::TmpTableName.'.company',
                    self::TmpTableName.'.remark',
                    self::TmpTableName.'.buyer_id',
                    self::TmpTableName.'.pay_status',
                    self::TmpTableName.'.discount_amount',
                    "refund_orders.created_at as refund_time",
                    "order_goods_details.coupon_amount as goods_coupon_amount",
                    "order_goods_details.coupon_type as goods_coupon_type",
                    "bilog.bi_fee_amount"
                ]);
                $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");


                $goodsQuery = DB::table($prefix . "order_goods_details")->select([
                    "out_trade_no", "coupon_type",
                    DB::raw("sum(coupon_amount) as coupon_amount"),
                ])
                    ->where("status", 1)
                    ->groupBy(["out_trade_no", "coupon_type"]);
                $this->setWhere($goodsQuery, [
                    "store_id" => $value,
                    "start_date" => $where["start_date"],
                    "end_date" => $where["end_date"]
                ]);

                $query->leftJoinSub($goodsQuery, $prefix . "order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");
                if (isset($where["sort"]) && $where["sort"] == 1) {
                    $query->orderBy(self::TmpTableName . ".total_amount", "desc");
                } else if (isset($where["sort"]) && $where["sort"] == 2) {
                    $query->orderBy(self::TmpTableName . ".total_amount", "asc");
                }

                /**
                 * 获取当前副表记录
                 */
                $biLog = DB::connection("bi");
                $biLog->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                $table = $biLog->table('bill_logs_'.date('Ym',strtotime($where['start_date'])));

                $biLogData = $table->select([
                    "out_trade_no",
                    DB::raw("sum(fee_amount) as bi_fee_amount")
                ])
                    ->where("store_id",$value)
                    ->groupBy("out_trade_no");
                $query->leftJoinSub($biLogData, $prefix.'bilog', self::TmpTableName . ".out_trade_no", "=", "bilog.out_trade_no");
                if (!$query->count())
                    continue;
                if ($query->count()>0)
                {
                    $flag = true;
                }
                $orders = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();
                Log::info('导出执行主表查询结束时间'.date("Y-m-d H:i:s", time()));

                $this->generatorExport($filePath, $fileName, $title, $flagTitle,$orders, function ($order) use ($company) {
                    $refund_type_name = "";
                    if ($order->pay_status == 6) {
                        $refund_type_name = "当日退";
                        if (isset($order->refund_time) && isset($order->refund_time)) {
                            if (date("Ymd", strtotime((string)$order->created_at)) != date("Ymd", strtotime((string)$order->refund_time))) {
                                $refund_type_name = "隔日退";
                            }
                            if (date("Ym", strtotime((string)$order->created_at)) != date("Ym", strtotime((string)$order->refund_time))) {
                                $refund_type_name = "隔月退";
                            }
                        }
                    }

                    $coupon_type = 0;
                    $coupon_type_text = "无";
                    $coupon_amount = 0;

                    if (!empty($order->goods_coupon_type) && $order->pay_status == 1) {
                        $coupon_type = $order->goods_coupon_type == "COUPON" ? 1 : 2;
                        $coupon_type_text = $order->goods_coupon_type == "COUPON" ? "预充" : "免充";
                        $coupon_amount = $order->goods_coupon_amount ?? 0;
                    }
                    if ($coupon_type == 0 && $order->discount_amount > 0 && $order->pay_status == 1) {
                        $coupon_type = 1;
                        $coupon_type_text = "预充";
                        $coupon_amount = $order->discount_amount;
                    }

                    $fee_amount = $order->fee_amount;
                    if (!empty($order->bi_fee_amount)) {
                        $fee_amount = $order->bi_fee_amount;
                    }

                    $actual_amount = bcsub((string)$order->total_amount, (string)$order->refund_amount, 2);
                    $receipt_amount = bcsub((string)$actual_amount, (string)$fee_amount, 2);
                    return [
                        iconv('utf-8','gbk',$order->store_id."\t"),
                        $order->store_name,
                        iconv('utf-8','gbk',$order->device_id."\t"),
                        $order->merchant_name,
                        "'" . $order->out_trade_no,
                        iconv('utf-8','gbk',$order->trade_no."\t"),
                        $order->total_amount,
                        $order->refund_amount,
                        $actual_amount,
                        $receipt_amount,
                        $order->rate . "%",
                        $order->fee_amount,
                        $order->ways_type_desc,
                        $order->pay_status_desc,
                        $order->pay_time,
                        isset($company[$order->company]) ? $company[$order->company]["company_desc"] : "",
                        $order->refund_time,
                        $refund_type_name,
                        $order->remark,
                        $order->buyer_id,
                        $coupon_type_text,
                        $coupon_amount,
                    ];
                });
                unset($query);
            }

            /**
             * 查询未有数据时生成空excel
             */
            if (!$flag)
            {
                $link = $this->emptyExcel($filePath,$fileName,$title);
                $log['link'] = $link;
                OrderExportRepo::getInstance()->saveData($log,$this->id);
                return;
            }

            /**
             * 防止未有数据时系统异常
             */
            if (file_exists($filePath.$fileName))
            {
                $fileSize = filesize($filePath.$fileName);
                Log::info('订单导出文件大小'.$fileSize);

                /**
                 * 对csv进行压缩
                 */
                $zipArc = new ZipArchive();
                $zipName = $filePath.explode('.',$fileName)[0].'.zip';
                if ($zipArc->open($zipName, ZipArchive::CREATE) === false) {
                    Log::info('订单导出文件打开csv失败');
                    return;
                }
                $zipFilaPath = $filePath.$fileName;
                // 加密文件 此处文件名及路径是压缩包内的
                $zipFile = '导出数据/'.$fileName;
                $zipArc->addFile($zipFilaPath,$zipFile);
                $zipArc->close();
                Log::info("[商户订单导出文件压缩关闭]");
                $url = "https://admin.zgjindoufu.com". "/uploads/csv/" . date("Y/md", time()) . "/".explode('.',$fileName)[0].'.zip';
            }

        }catch (\Exception $e){
            Log::error('商户订单导出异常'.$e->getMessage());
        }

        /**
         * 删除excel文件
         */
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }
        Log::info('导出执行结束时间'.date("Y-m-d H:i:s", time()));

        $log['link'] = $url;
        OrderExportRepo::getInstance()->saveData($log,$this->id);
    }


    protected function setWhere($query, array $where, string $table = "")
    {
        if (!empty($table)) {
            $table .= ".";
        }

        if (!empty($where["merchant_all_store_ids"])) {
            $query->whereIn($table . "store_id", $where["merchant_all_store_ids"]);
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn($table . "store_id", $where["store_id"]);
            } else {
                $query->where($table . "store_id", $where["store_id"]);
            }
        }
        if (!empty($where["merchant_id"])) {
            $query->where($table . "merchant_id", $where["merchant_id"]);
        }

        if (!empty($where["company"])) {
            $query->where($table . "company", $where["company"]);
        }


        if (!empty($where["start_date"])) {
            $query->where($table . "created_at", ">=", $where["start_date"]);
        }

        if (!empty($where["end_date"])) {
            $query->where($table . "created_at", "<=", $where["end_date"]);
        }

        if (!empty($where["trade_no"])) {
            $query->where($table . "trade_no", "=", $where["trade_no"]);
        }

        if (!empty($where["out_trade_no"])) {
            $query->where($table . "out_trade_no", "=", $where["out_trade_no"]);
        }

        if (!empty($where["device_id"])) {
            $query->where($table . "device_id", "=", $where["device_id"]);
        }
        if (!empty($where["ways_source"])) {
            $query->where($table . "ways_source", "=", $where["ways_source"]);
        }
        if (!empty($where["ways_type"])) {
            $query->where($table . "ways_type", "=", $where["ways_type"]);
        }

        if (isset($where["discount"]) && $where["discount"] == 1) {
            $query->where($table . "discount_amount", ">", 0);
        }

        if (!empty($where["auth_code"])) {
            $query->where($table . "auth_code", "=", $where["auth_code"]);
        }

        if (!empty($where["qwx_no"])) {
            $query->where($table . "qwx_no", "=", $where["qwx_no"]);
        }

        if (!empty($where["pay_status"])) {
            $query->where($table . "pay_status", "=", $where["pay_status"]);
        }

    }

    /**
     * 获取表名称
     * @param string $time
     * @param bool $as
     * @param string $prefix
     * @return string
     */
    protected  function getTableName(string $time, bool $as = true, string $prefix = ""): string
    {
        $tableName = $prefix . "orders_" . date("Ym", strtotime($time));
        if (!$as) {
            return $tableName;
        }
        return $tableName . " as " . $prefix . self::TmpTableName;
    }

    protected function generatorExport(string $filePath, string $fileName, $title,$flagTitle, \Generator $orders, callable $callable): void
    {
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $file = $filePath . $fileName;
        $fileObj = fopen($file, 'a+');
        fwrite($fileObj, "\xEF\xBB\xBF"); // UTF-8 BOM
        if ($flagTitle)
        {
            fputcsv($fileObj, $title);
        }

        $counter = 0;
        foreach ($orders as $order) {
            fputcsv($fileObj, $callable($order));
            // 每10000行刷新一次文件缓冲
            if (++$counter % 1000 === 0) {
                fflush($fileObj);
                flush();
                gc_collect_cycles();
                // 可选：记录进度
                Log::info("Processed {$counter} records");
            }
            unset($order);
        }
        fclose($fileObj);
    }

    /**
     * 未有数据返回空excel
     * @param string $filePath
     * @param string $fileName
     * @param $title
     * @return void
     */
    public function emptyExcel(string $filePath, string $fileName, $title)
    {
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $file = $filePath . $fileName;
        $fileObj = fopen($file, 'a+');
        fwrite($fileObj, "\xEF\xBB\xBF"); // UTF-8 BOM
        fputcsv($fileObj, $title);
        fclose($fileObj);

        /**
         * 对csv进行压缩
         */
        $zipArc = new ZipArchive();
        $zipName = $filePath.explode('.',$fileName)[0].'.zip';
        if ($zipArc->open($zipName, ZipArchive::CREATE) === false) {
            Log::info('订单导出文件打开csv失败');
            return;
        }
        $zipFilaPath = $filePath.$fileName;
        // 加密文件 此处文件名及路径是压缩包内的
        $zipFile = '导出数据/'.$fileName;
        $zipArc->addFile($zipFilaPath,$zipFile);
        $zipArc->close();
        Log::info("[商户订单导出文件压缩关闭]");
        $url = "https://admin.zgjindoufu.com". "/uploads/csv/" . date("Y/md", time()) . "/".explode('.',$fileName)[0].'.zip';
        return $url;
    }
}